{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# 安装 SQLAlchemy 并连接到数据库\r\n",
    "\r\n",
    "SQLAlchemy can be used with Python 2.7 or later. In this tutorial, we are using Python 3.5. However, you are free to use any version of Python 3.\r\n",
    "\r\n",
    "SQLAlchemy 可以与 Python 2.7或更高版本一起使用。在中文翻译中，我们使用的是 Python 3.9。但是，您可以自由使用任何版本的 Python 3。\r\n",
    "\r\n",
    "注:  jupyter notebook 使用的是python 3.9 SQLAlchemy 1.3.24\r\n",
    "\r\n",
    "(SQLAlchemy 1.4 是 SQLAlchemy2.0 的预备版本,有重大变更, 不保证能运行)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Installing SQLAlchemy 安装 SQLAlchemy\r\n",
    "\r\n",
    "To install SQLAlchemy type the following:\r\n",
    "\r\n",
    "要安装 SQLAlchemy 类型如下:\r\n",
    "\r\n",
    "pip install sqlalchemy\r\n",
    "To verify whether the installation was successful or not enter the following in the Python shell.\r\n",
    "\r\n",
    "要验证安装是否成功，请在 Python shell 中输入以下内容。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import sqlalchemy\r\n",
    "sqlalchemy.__version__"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'1.3.24'"
      ]
     },
     "metadata": {},
     "execution_count": 1
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Installing DBAPI 安装 DBAPI\r\n",
    "\r\n",
    "SQLAlchemy by default only works with SQLite database without any additional drivers. To work with other databases you have to install a DBAPI complaint driver specific to the database.\r\n",
    "\r\n",
    "默认情况下，SQLAlchemy 只能在 SQLite 数据库上工作，不需要任何额外的驱动程序。要使用其他数据库，您必须安装特定于数据库的 DBAPI 抱怨驱动程序。\r\n",
    "\r\n",
    "What is DBAPI?\r\n",
    "\r\n",
    "什么是 DBAPI？\r\n",
    "\r\n",
    "A DBAPI is a standard which encourages the use of the same API to work with a wide variety of databases. The following table lists some databases and their DBAPI complaint drivers.\r\n",
    "\r\n",
    "DBAPI 是一个标准，它鼓励使用相同的 API 来处理各种数据库。下表列出了一些数据库及其 DBAPI 兼容驱动程序。\r\n",
    "\r\n",
    "| Database             | DBAPI driver                                                 |\r\n",
    "| :------------------- | :----------------------------------------------------------- |\r\n",
    "| MySQL                | [PyMySQL](https://github.com/PyMySQL/PyMySQL), [MySQL-Connector](https://dev.mysql.com/downloads/connector/python/), [CyMySQL](https://github.com/nakagami/CyMySQL), [MySQL-Python](https://sourceforge.net/projects/mysql-python/) (default) |\r\n",
    "| PostgreSQL           | [psycopg2](https://pypi.python.org/pypi/psycopg2/) (default), [pg8000](https://github.com/mfenniak/pg8000), |\r\n",
    "| Microsoft SQL Server | [PyODBC](https://pypi.python.org/pypi/pyodbc/) (default), [pymssql](http://pymssql.org/en/stable/) |\r\n",
    "| Oracle               | [cx-Oracle](https://oracle.github.io/python-cx_Oracle/) (default) |\r\n",
    "| Firebird             | [fdb](https://pypi.python.org/pypi/fdb/) (default), [kinterbasdb](https://www.firebirdsql.org/en/python-driver/) |"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "本教程中的所有示例都是针对 PostgreSQL 进行测试的，但是您可以使用任何您想要的数据库。\r\n",
    "\r\n",
    "要安装 psycopg2 DBAPI for PostgreSQL，输入以下命令:\r\n",
    "\r\n",
    "pip install psycopg2\r\n",
    "\r\n",
    "注:  jupyter notebook 版本 使用的是mysql \r\n",
    "\r\n",
    "pip install pymysql"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Creating Engine 创造引擎\r\n",
    "\r\n",
    "The first step to connect to the database is to create an Engine object. The Engine object is how we interact with the database. It consists of two components: Dialect and a Connection Pool.\r\n",
    "\r\n",
    "连接到数据库的第一步是创建 Engine 对象。Engine 对象是我们与数据库交互的方式。它由两部分组成: 方言和连接池。\r\n",
    "\r\n",
    "SQLAlchemy Dialect 方言\r\n",
    "It is said that the SQL is a standard language for accessing databases. In truth, the SQL syntax varies from one database to another. Databases vendors rarely stick to the standard and prefer to add their own twist and turn to the language. For example, if we are using Firebird then the SQL query to select id and name of the first 5 rows from the employees table would be:\r\n",
    "\r\n",
    "据说 SQL 是访问数据库的标准语言。实际上，SQL 语法在不同数据库之间存在差异。数据库供应商很少坚持这一标准，他们更喜欢添加自己的特色，转而使用这种语言。例如，如果我们使用 Firebird，那么从 employees 表中选择 id 和前5行名称的 SQL 查询将是:\r\n",
    "\r\n",
    "select first 10 id, name from employees\r\n",
    "The equivalent query for the MySQL database would be:\r\n",
    "\r\n",
    "对于 MySQL 数据库的等效查询是:\r\n",
    "\r\n",
    "select id, name from employees limit 10\r\n",
    "To handle these differences we need Dialect. The Dialect defines the behavior of the database. In other words, it handles things like generating SQL statements, execution, result-set handling and so on. Once the appropriate driver is installed, the Dialect handles all these differences for us so that we can just focus on writing the application.\r\n",
    "\r\n",
    "为了处理这些差异，我们需要方言。方言定义数据库的行为。换句话说，它处理诸如生成 SQL 语句、执行、结果集处理等等。一旦安装了合适的驱动程序，方言就会为我们处理所有这些差异，这样我们就可以专注于编写应用程序了。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# SQLAlchemy Connection Pool 连接池\r\n",
    "\r\n",
    "The connection pool a standard way of caching connections in the memory so that they can be reused. Creating a new connection everytime an application wants to talk to the database is expensive and time-consuming. The connection pool if implemented correctly offers a significant performance boost.\r\n",
    "\r\n",
    "连接池是在内存中缓存连接的标准方式，以便可以重用这些连接。每次应用程序希望与数据库通信时创建一个新连接，这既费时又费钱。连接池如果实现正确，将大大提高性能。\r\n",
    "\r\n",
    "In connection pooling whenever an application needs to talk to the database it fetches the connection from the pool. After performing the desired queries application releases the connection and pushes it back to the pool. In case all the connections are being used, a new connection is created and added to the pool.\r\n",
    "\r\n",
    "在连接池中，每当应用程序需要与数据库通信时，它都会从池中获取连接。在执行所需的查询应用程序之后，释放连接并将其推回池。如果正在使用所有连接，则创建一个新连接并将其添加到池中。\r\n",
    "\r\n",
    "To create engine (i.e Engine object), we use create_engine() function of the sqlalchemy package. At its simplest, it accepts the connection string. The connection string provides information about the data source. Its general format is as follows:\r\n",
    "\r\n",
    "为了创建 Engine (即 Engine 对象) ，我们使用 sqlalchemy 包的 create _ Engine ()函数。最简单地说，它接受连接字符串。连接字符串提供有关数据源的信息。其一般格式如下:\r\n",
    "\r\n",
    "dialect+driver://username:password@host:port/database\r\n",
    "The dialect refers to the name of the database like mysql, postgresql, mssql, oracle and so on. The driver refers to the DBAPI you are using. The driver is optional, if not specified a default driver will be used (assuming it is already installed). The username and password are the credentials to login to the database server. The host is the location of the database server. The port refers to the optional database port and database is the name of the database you want to connect to.\r\n",
    "\r\n",
    "方言指的是数据库的名称，如 mysql、 postgresql、 mssql、 oracle 等。驱动程序引用您正在使用的 DBAPI。驱动程序是可选的，如果没有指定，将使用默认驱动程序(假设已经安装)。用户名和密码是登录到数据库服务器的凭据。主机是数据库服务器的位置。端口是指可选的数据库端口，数据库是要连接到的数据库的名称。\r\n",
    "\r\n",
    "Here is the code to create an engine for some popular databases:\r\n",
    "\r\n",
    "下面是为一些流行数据库创建引擎的代码:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "from sqlalchemy import  create_engine\r\n",
    "\r\n",
    "# Connecting to MySQL server at localhost using PyMySQL DBAPI \r\n",
    "engine = create_engine(\"mysql+pymysql://root:flask123@localhost/mydb\")\r\n",
    "\r\n",
    "# Connecting to MySQL server at 23.92.23.113 using mysql-python DBAPI \r\n",
    "# engine = create_engine(\"mysql+mysqldb://root:pass@23.92.23.113/mydb\")\r\n",
    "\r\n",
    "# Connecting to PostgreSQL server at localhost using psycopg2 DBAPI \r\n",
    "# engine = create_engine(\"postgresql+psycopg2://root:pass@localhost/mydb\")\r\n",
    "\r\n",
    "# Connecting to Oracle server at localhost using cx-Oracle DBAPI\r\n",
    "# engine = create_engine(\"oracle+cx_oracle://root:pass@localhost/mydb\")\r\n",
    "\r\n",
    "# Connecting to MSSQL server at localhost using PyODBC DBAPI\r\n",
    "# engine = create_engine(\"oracle+pyodbc://root:pass@localhost/mydb\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The format of the connection string for the SQLite database is slightly different. Since SQLite is a file based database we don't specify username, password, host and port in the connection string. Here is the code to create an engine for the SQLite database.\r\n",
    "\r\n",
    "SQLite 数据库的连接字符串的格式略有不同。因为 SQLite 是一个基于文件的数据库，所以我们不在连接字符串中指定用户名、密码、主机和端口。下面是为 SQLite 数据库创建引擎的代码。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "# from sqlalchemy import  create_engine\r\n",
    "\r\n",
    "# engine = create_engine('sqlite:///sqlite3.db') # using relative path\r\n",
    "# engine = create_engine('sqlite:////path/to/sqlite3.db') # using absolute path"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Connecting to the Database 连接到数据库\r\n",
    "\r\n",
    "Note that creating an engine doesn't instantly establish a connection to the database. To obtain the connection we use connect() method of the Engine object which returns an object of type Connection.\r\n",
    "\r\n",
    "请注意，创建引擎并不能立即建立到数据库的连接。要获得连接，我们使用 Engine 对象的 connect ()方法，该方法返回一个 Connection 类型的对象。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "from sqlalchemy import create_engine\r\n",
    "engine = create_engine(\"mysql+pymysql://root:flask123@localhost/mydb\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Here is the complete code:\r\n",
    "\r\n",
    "下面是完整的代码:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "from sqlalchemy import create_engine\r\n",
    "\r\n",
    "engine = create_engine(\"mysql+pymysql://root:flask123@localhost/mydb\")\r\n",
    "engine.connect()\r\n",
    "\r\n",
    "print(engine)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Engine(mysql+pymysql://root:***@localhost/mydb)\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "If you try to run the above code you will get the following error:\r\n",
    "\r\n",
    "如果你尝试运行上面的代码，你可能会得到以下错误:\r\n",
    "\r\n",
    "```\r\n",
    "OperationalError: (psycopg2.OperationalError) FATAL:  database \"sqlalchemy_tuts\" does not exist (Background on this error at: http://sqlalche.me/e/e3q8)\r\n",
    "The problem is that we are trying to connect to a database which doesn't exist. To create the database login to PostgreSQL server and execute the following command:\r\n",
    "```\r\n",
    "\r\n",
    "问题是我们正在尝试连接到一个不存在的数据库。创建数据库登录到 PostgreSQL 服务器，并执行以下命令:\r\n",
    "\r\n",
    "```bash\r\n",
    "$ sudo -su postgres psql\r\n",
    "postgres=# \r\n",
    "postgres=# create database sqlalchemy_tuts;\r\n",
    "CREATE DATABASE\r\n",
    "postgres=# \r\n",
    "postgres=# \\q\r\n",
    "Run the script again and this time you should get the following output:\r\n",
    "```\r\n",
    "\r\n",
    "再次运行脚本，这次你应该会得到如下输出:\r\n",
    "\r\n",
    "Engine(postgres+psycopg2://postgres:***@localhost/sqlalchemy_tuts)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Some Additional Arguments 一些附加的参数\r\n",
    "\r\n",
    "The following table lists some additional keyword arguments that you can pass to the create_engine() function.\r\n",
    "\r\n",
    "下表列出了可以传递给 `create_engine()` 函数的一些附加关键字参数。\r\n",
    "\r\n",
    "| Argument          | Description                                                  |\r\n",
    "| :---------------- | :----------------------------------------------------------- |\r\n",
    "| `echo`            | A boolean argument if set to `True`, the engine will log all the SQL it is currently executing to the standard output. By default, it is set to `False`. |\r\n",
    "| `pool_size`       | It specifies the number of connection to keep in the connection pool. Its default value is 5. |\r\n",
    "| `max_overflow`    | It specifies the number of connections that can be opened beyond the `pool_size` setting, by default it is set to 10. |\r\n",
    "| `encoding`        | It specifies the encoding to use by SQLAlchemy. By default, it is set to `utf-8`. Note that it doesn't control the encoding scheme of the database. |\r\n",
    "| `isolation_level` | The isolation level to use. This setting control how a transaction will be isolated from the other transactions. Different databases support different isolation levels. To learn more about the isolation levels check the database documentation.\r\n",
    "| `echo`            | 一个布尔参数，如果设置为 ，引擎将记录当前执行到标准输出的所有 SQL。默认情况下，它被设置为`False`. |\r\n",
    "| `pool_size`       | 它指定连接池中要保留的连接数，默认值为5                      |\r\n",
    "| `max_overflow`    | 方法之外可以打开的连接数 设置，默认设置为10                  |\r\n",
    "| `encoding`        | 它指定 SQLAlchemy 使用的编码 。请注意，它不控制数据库的编码方案 |\r\n",
    "| `isolation_level` | 要使用的隔离级别。此设置控制事务如何与其他事务隔离。不同的数据库支持不同的隔离级别。要了解有关隔离级别的更多信息，请查看数据库文档 |\r\n",
    "\r\n",
    "Here is a script which uses some of these additional keyword arguments while creating an engine.\r\n",
    "\r\n",
    "下面是一个脚本，它在创建引擎时使用一些附加的关键字参数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "from sqlalchemy import create_engine\r\n",
    "\r\n",
    "engine = create_engine(\r\n",
    "    \"mysql+pymysql://root:flask123@localhost/sqlalchemy_tuts\", \r\n",
    "    echo=True, pool_size=6, max_overflow=10, encoding='utf8'\r\n",
    ")\r\n",
    "\r\n",
    "engine.connect()\r\n",
    "\r\n",
    "print(engine)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2021-08-23 09:09:53,314 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'\n",
      "2021-08-23 09:09:53,315 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-08-23 09:09:53,323 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'\n",
      "2021-08-23 09:09:53,325 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-08-23 09:09:53,331 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()\n",
      "2021-08-23 09:09:53,332 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-08-23 09:09:53,334 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'\n",
      "2021-08-23 09:09:53,335 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-08-23 09:09:53,338 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1\n",
      "2021-08-23 09:09:53,339 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-08-23 09:09:53,342 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1\n",
      "2021-08-23 09:09:53,342 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-08-23 09:09:53,344 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1\n",
      "2021-08-23 09:09:53,345 INFO sqlalchemy.engine.base.Engine {}\n",
      "Engine(mysql+pymysql://root:***@localhost/sqlalchemy_tuts)\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "[粗翻]  2021-08-24 Fully jupyter notebook and Chinese Verion CRAFTED BY YULK "
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.6 64-bit"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "interpreter": {
   "hash": "c644d696b95f5e0f4df3c6556741cf30bcf9ea6ca93c3e1f29fcf31d885534fc"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}